const { pool } = require('../config/db');

// 创建用户完整信息表
exports.createUserProfileTable = async () => {
  const query = `
    CREATE TABLE IF NOT EXISTS user_profiles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      openid VARCHAR(100) NOT NULL,
      unionid VARCHAR(100),
      nickName VARCHAR(100),
      avatarUrl VARCHAR(255),
      gender INT,
      language VARCHAR(50),
      city VARCHAR(100),
      province VARCHAR(100),
      country VARCHAR(100),
      raw_data JSON,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      UNIQUE KEY unique_openid (openid)
    )
  `;
  
  try {
    await pool.execute(query);
    console.log('User profile table created successfully!');
  } catch (error) {
    console.error('Error creating user profile table:', error);
  }
};

// 创建或更新用户完整信息
exports.createOrUpdateUserProfile = async (profileData) => {
  const { 
    user_id, 
    openid, 
    unionid, 
    nickName, 
    avatarUrl, 
    gender, 
    language, 
    city, 
    province, 
    country, 
    raw_data 
  } = profileData;
  
  // 检查是否已存在该用户的完整信息
  const checkQuery = 'SELECT id FROM user_profiles WHERE user_id = ?';
  const [existingProfiles] = await pool.execute(checkQuery, [user_id]);
  
  if (existingProfiles.length > 0) {
    // 更新现有用户信息
    const updateQuery = `
      UPDATE user_profiles 
      SET openid = ?, unionid = ?, nickName = ?, avatarUrl = ?, gender = ?, 
          language = ?, city = ?, province = ?, country = ?, raw_data = ?,
          updated_at = CURRENT_TIMESTAMP
      WHERE user_id = ?
    `;
    await pool.execute(updateQuery, [
      openid, unionid, nickName, avatarUrl, gender, 
      language, city, province, country, raw_data, 
      user_id
    ]);
    
    return { id: existingProfiles[0].id, ...profileData };
  } else {
    // 创建新的用户信息
    const insertQuery = `
      INSERT INTO user_profiles (
        user_id, openid, unionid, nickName, avatarUrl, gender, 
        language, city, province, country, raw_data
      ) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    const [result] = await pool.execute(insertQuery, [
      user_id, openid, unionid, nickName, avatarUrl, gender, 
      language, city, province, country, raw_data
    ]);
    
    return { id: result.insertId, ...profileData };
  }
};

// 根据用户ID获取用户完整信息
exports.getUserProfileByUserId = async (userId) => {
  const query = 'SELECT * FROM user_profiles WHERE user_id = ?';
  const [rows] = await pool.execute(query, [userId]);
  return rows[0] || null;
};